package com.example.demo.security.sysUser.dao;

import com.example.demo.db.BaseDao;
import com.example.demo.security.sysUser.entity.UserBean;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.*;

@Repository
public class UserDao extends BaseDao<UserBean,Integer> {

    /**
     * 根据姓名查询用户
     * @author 自嘲
     * @param name
     * @return
     */
    public UserBean queryByName(String name){
        String sql="SELECT * FROM userInfo WHERE state=1 ";
        List<Object> params=new ArrayList<>();
        if (!name.isEmpty()){
            sql+="AND (loginName=? OR phone= ? ) ";
            params.add(name);
            params.add(name);
        }
        try {
            UserBean userBean=getJtl().queryForObject(sql,new BeanPropertyRowMapper<UserBean>(UserBean.class),params.toArray());
            return userBean;
        }catch (Exception e){
            return null;
        }
    }

    /**
     * 注册用户
     * @author 自嘲
     * @param userBean
     * @return
     */
    public Integer registerUser(UserBean userBean){
        SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String endTime=simpleDateFormat.format(userBean.getEndTime());
        String sql="INSERT INTO userInfo(loginName,userName,userPwd,phone,email,headPortrait,endTime,state) VALUES('"
                +userBean.getLoginName()+"','"+userBean.getUserName()+"','"+userBean.getUserPwd()+"','"+userBean.getPhone()+"','"+userBean.getEmail()+"','"+userBean.getHeadPortrait()+"','"+endTime+"','"+userBean.getState()+"')";
        return getJtl().update(sql);
    }

    /**
     * 查询用户加入的群聊
     * @author 自嘲
     * @param id
     * @return
     */
    public List<UserBean> queryFriends(Integer id){
        String sql="SELECT * FROM userInfo WHERE id IN (SELECT friendId FROM friendsInfo WHERE state=1 ";
        List<Object> params=new ArrayList<>();
        if (id!=null){
            sql+="AND myId=? ";
            params.add(id);
        }
        sql+=" )";
        return getJtl().query(sql,new BeanPropertyRowMapper<UserBean>(UserBean.class),params.toArray());
    }

    /**
     * 将用户改为在线
     * @author 自嘲
     * @param id
     * @return
     */
    public Integer updateUserStateOnLine(Integer id){
        String sql="UPDATE userInfo SET state=2,endTime=? WHERE id= ?";
        List<Object> params=new ArrayList<>();
        params.add(new Date());
        params.add(id);
        return getJtl().update(sql,params.toArray());
    }

    /**
     * 根据id查询用户名
     * @author 自嘲
     * @param id
     * @return
     */
    public UserBean queryUserNameById(Integer id){
        String sql="SELECT * FROM userInfo WHERE id=? ";
        return getJtl().queryForObject(sql,new BeanPropertyRowMapper<>(UserBean.class),id);
    }

    /**
     * 将用户状态改为离线
     * @author 自嘲
     * @param id
     * @return
     */
    public Integer updateUserState(Integer id){
        String sql="UPDATE userInfo SET state=1 WHERE id= ?";
        return getJtl().update(sql,id);
    }

    public List<UserBean> queryFriendsById(Integer id,String name){
        String sql="SELECT * FROM userInfo WHERE (loginName LIKE '%"+name+"%' OR userName LIKE '%"+name+"%' OR phone LIKE '%"+name+"%' OR email LIKE '%"+name+"%') AND (state=1 OR state=2) AND id!=? AND id NOT IN (SELECT friendId FROM friendsInfo WHERE myId=?) AND id NOT IN (SELECT myId FROM friendsInfo WHERE friendId=?) ";
        List<Object> params=new ArrayList<>();
        params.add(id);
        params.add(id);
        params.add(id);
        return getJtl().query(sql, new BeanPropertyRowMapper<>(UserBean.class),params.toArray());
    }

    public Integer addFriend(Integer userId,Integer id){
        String sql="INSERT INTO friendsInfo(myId,friendId,state) VALUES("+userId+", "+id+", 2) ";
        return getJtl().update(sql);
    }

    public List<UserBean> queryAddMessageById(Integer id){
        String sql="SELECT * FROM userInfo WHERE id IN (SELECT myId FROM friendsInfo WHERE state=2 AND friendId=? )";
        return getJtl().query(sql,new BeanPropertyRowMapper<>(UserBean.class),id);
    }

    public List<UserBean> queryNotInGroup(Integer id,Integer groupId){
        String sql="SELECT * FROM userInfo WHERE id IN(SELECT friendId FROM friendsInfo WHERE state=1 AND myId=?) AND id NOT IN(SELECT userId FROM groupManagement WHERE groupId=?)";
        List<Object> params=new ArrayList<>();
        params.add(id);
        params.add(groupId);
        return getJtl().query(sql,new BeanPropertyRowMapper<>(UserBean.class),params.toArray());
    }

    public Integer agreeMessage(Integer userId,Integer id){
        String sql="UPDATE friendsInfo SET state=1 WHERE friendId="+userId+" AND myId="+id;
        String sql1="INSERT INTO friendsInfo(myId,friendId,state) VALUES("+userId+", "+id+", 1)";
        Integer i=getJtl().update(sql);
        i+=getJtl().update(sql1);
        return i;
    }

    public Integer disagreeMessage(Integer userId,Integer id){
        String sql="DELETE FROM friendsInfo WHERE friendId="+userId+" AND myId="+id;
        return getJtl().update(sql);
    }

    public Integer exitFriend(Integer id,Integer friendId){
        String sql="DELETE FROM friendsInfo WHERE (myId=? AND friendId=?) OR (friendId=? AND myId=?)";
        List<Object> params=new ArrayList<>();
        params.add(id);
        params.add(friendId);
        params.add(id);
        params.add(friendId);
        return getJtl().update(sql,params.toArray());
    }
}
